﻿using Common;
using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Dapper;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace DbControl
{
    public class OprDevice
    {
        #region Devices
        public static List<devices> SelectdevicesAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT [id]
                                ,[parentid]
                                ,[device_code]
                                ,[device_name]
                                ,[device_position]
                                ,[device_type]
                                ,[device_mode]
                                ,[device_factory]
                                ,[install_time]
                                ,[oil_id]
                                ,[sensor_id]
                                ,[parameter_id]
                                ,[warn_id]
                                ,[create_time]
                                ,[flag]
                            FROM devices ";
                    return connection.Query<devices>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        /// <summary>
        /// 模糊查找
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public static DataTable GetdevicesByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,create_time 
                        ,flag 
                        FROM devices";
                }
                else
                {
                    sql = string.Format(@"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,create_time 
                        ,flag 
                        FROM devices WHERE  device_name like '%{0}%'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        /// <summary>
        /// 根据id 或者 parentid 查找其信息
        /// </summary>
        /// <param name="parentId"></param>
        /// <param name="isParent"></param>
        /// <returns></returns>
        public static DataTable GetdevicesByTreeNode(int parentId,bool isParent)
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql ="";                   
                    if (isParent)
                    {
                        sql = @"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,create_time 
                        ,flag 
                        FROM devices WHERE id In (SELECT id FROM devices WHERE parentid=@parentid)";
                    }
                    else
                    {
                        sql = @"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,create_time 
                        ,flag 
                        FROM devices Where id=@parentid";
                    }
                    dt.Load(connection.ExecuteReader(sql,new { parentid=parentId }));
                }
            }
            catch (Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        /// <summary>
        /// 设备树
        /// </summary>
        /// <returns></returns>
        public static DataTable GetdevicesTree()
        {
            DataTable dt = new DataTable();
            try 
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT [id]
                                ,[parentid]     
                                ,[device_name]    
                                ,[flag]
                                FROM [devices] where flag>0";                    
                    
                    dt.Load(connection.ExecuteReader(sql));                    
                }
            }
            catch(Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!"+ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        /// <summary>
        /// 根据父节点查找其子节点设备
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public static DataTable GetdevicesByParentId(int parentId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT * FROM [devices] where flag>0 AND parentid=@parentid ";

                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        
        public static devices GetdevicesById(int Id)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,warn_ids 
                        ,create_time 
                        ,flag 
                        FROM devices Where id=@id ";
                return connection.Query<devices>(sql, new { id = Id }).SingleOrDefault();
            }
        }
        /// <summary>
        /// 编码 识别码
        /// </summary>
        /// <param name="device_code"></param>
        /// <returns></returns>
        public static devices GetDeviceByDeviceId(string device_code)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id 
                        ,parentid 
                        ,device_code 
                        ,device_name 
                        ,device_position 
                        ,device_type 
                        ,device_mode 
                        ,device_factory 
                        ,install_time 
                        ,oil_id 
                        ,sensor_id 
                        ,parameter_id 
                        ,warn_id 
                        ,create_time 
                        ,flag 
                        FROM devices Where device_code=@device_code ";
                return connection.Query<devices>(sql, new { device_code = device_code }).SingleOrDefault();
            }
        }
        /// <summary>
        /// 编码/识别码是否存在 唯一性
        /// </summary>
        /// <param name="device_code"></param>
        /// <returns></returns>
        public static bool IsExistdeviceId(string device_code)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from devices WHERE  device_code =@device_code ";
                    intNum = (int)connection.ExecuteScalar(sql, new { device_code = device_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistdeviceName(string device_name)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from devices WHERE  device_name =@device_name ";
                    intNum = (int)connection.ExecuteScalar(sql, new { device_name = device_name });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistDevicetopic(string topic)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(Id)  from Device WHERE  topic =@topic ";
                    intNum = (int)connection.ExecuteScalar(sql, new { topic = topic });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static int DeleteById(int id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM devices WHERE id=@id";
                    sql = @"UPDATE devices SET flag = 0 WHERE id=@id";
                    return connection.Execute(sql, new { id = id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int DeleteByParentId(int parentid)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM devices WHERE parentid=@parentid";
                    sql = @"UPDATE devices SET flag = 0 WHERE parentid=@parentid";
                    return connection.Execute(sql, new { parentid = parentid });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Update(devices dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE devices
                                   SET parentid=@parentid
                                      ,device_code=@device_code
                                      ,device_name=@device_name
                                      ,device_position=@device_position
                                      ,device_type=@device_type
                                      ,device_mode=@device_mode
                                      ,device_factory=@device_factory
                                      ,install_time=@install_time
                                      ,oil_id=@oil_id
                                      ,sensor_id=@sensor_id
                                      ,parameter_id=@parameter_id
                                      ,warn_id=@warn_id
                                      ,create_time=@create_time
                                      ,flag=@flag
                                 WHERE id=@id";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Insert(devices dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"INSERT INTO devices
                                (parentid 
                                ,device_code 
                                ,device_name 
                                ,device_position 
                                ,device_type 
                                ,device_mode 
                                ,device_factory 
                                ,install_time 
                                ,oil_id 
                                ,sensor_id 
                                ,parameter_id 
                                ,warn_id 
                                ,create_time 
                                ,flag)
                            VALUES
                                (@parentid
                                ,@device_code
                                ,@device_name
                                ,@device_position
                                ,@device_type
                                ,@device_mode
                                ,@device_factory
                                ,@install_time
                                ,@oil_id
                                ,@sensor_id
                                ,@parameter_id
                                ,@warn_id
                                ,@create_time
                                ,@flag)";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        #endregion

    }
}
